package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Set;

import cn.jhc.bean.EventBean;
import cn.jhc.bean.MusicBean;
import cn.jhc.bean.UserBean;

public class NewUserDao {
	
	private Connection con = null;
	int PageCount;
	int UserNum;
	String Displayname;
	String Vote;
	
	public void addNewUser( String name, String password,String displayname,String email,String phone,String vote) {
		String sql = "insert into user(name,password,displayname,email,phone,vote,date,integral) "
				+ "values(?,password(?),?,?,?,?,now(),0)";
		PreparedStatement st = null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.prepareStatement(sql);			
			st.setString(1,name);
			st.setString(2, password);
			st.setString(3, displayname);
			st.setString(4,email);
			st.setString(5,phone);
			st.setString(6,vote);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public ArrayList<UserBean> getAllUser(int page,int titleCount) {
		ArrayList<UserBean> list = new ArrayList<UserBean>();
		String sql = "select id,name,displayname,email,phone,date,vote,integral from user order by id desc limit "
				+ (page-1) * (titleCount) + "," + titleCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);	
			while (result.next()) {
				UserBean u = new UserBean();
				u.setId(result.getInt(1));
				u.setName(result.getString(2));
				u.setDisplayname(result.getString(3));
				u.setEmail(result.getString(4));
				u.setPhone(result.getString(5));
				u.setDate(result.getDate(6));
				u.setVote(result.getString(7));
				u.setIntegral(result.getInt(8));
				list.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public String getDisplayname(int id){
		String sql="select displayname from user where id = ?";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				this.Displayname=(result.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Displayname;
	}
	public int getUserCount(int titleCount) {
		String sql = "select count(*) from user";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%titleCount==0){
					this.PageCount=(result.getInt(1)/titleCount);
				}else{
					this.PageCount=(result.getInt(1)/titleCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}
	
	public void DeleteUser(int id) {
		String sql = "delete from user where id= ? ";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void ToEvent(String classname,String room,String name,String phone,String email,int act_id,int userid){
		
		String sql = "insert into event (classname,room,name,phone,email,act_id,userid,date) "
				+ "values(?,?,?,?,?,?,?,now())";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setString(1, classname);
			st.setString(2, room);
			st.setString(3, name);
			st.setString(4, phone);
			st.setString(5, email);
			st.setInt(6, act_id);
			st.setInt(7, userid);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public ArrayList<EventBean> getAllEvent(int act_id,int page,int titleCount){
		ArrayList<EventBean> list = new ArrayList<EventBean>();
		String sql = "select id,name,classname,room,phone,email,date from event where act_id= "+act_id+" order by id desc limit "
				+(page-1) * (titleCount)+","+titleCount+"";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);
				while(result.next()){
					EventBean e = new EventBean();
					e.setId(result.getInt(1));
					e.setName(result.getString(2));
					e.setClassname(result.getString(3));
					e.setRoom(result.getString(4));
					e.setPhone(result.getString(5));
					e.setEmail(result.getString(6));
					e.setDate(result.getDate(7));
					list.add(e);
				}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public void deleteEvent(int id) {
		String sql = "delete from event where id = ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public int getCount( int act_id,int titleCount) {
		String sql = "select count(*) from event where act_id = ?";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, act_id);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%titleCount==0){
					this.PageCount=(result.getInt(1)/titleCount);
				}else{
					this.PageCount=(result.getInt(1)/titleCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}
	public int getAllCount( int act_id) {
		String sql = "select count(*) from event where act_id = ?";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, act_id);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				this.PageCount=result.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return PageCount ;
	}
	public String getVote(int userid){
		String sql="select vote from user where id="+userid+"";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);	
			if (!result.next())
				result = null;
			else {
				this.Vote=(result.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Vote;
	}
	public void updateVote(int userid){
		String sql="update user set vote = 'yes' where id= ?";
		System.out.println(userid +"vote yes");
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, userid);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void updateYesVote(int userid){
		String sql="update user set vote = 'no' where id= ?";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, userid);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void updateAllVote(){
		String sql="update user set vote = 'no'";
		PreparedStatement st = null;
		try {
			st = con.prepareStatement(sql);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public int getAllUser(){
		String sql="select count(*) from user";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if(!result.next()){
				result=null;
			}else{
				this.UserNum=result.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return this.UserNum;
	}
	public ArrayList<UserBean> SearchUser(String name) {
		ArrayList<UserBean> list = new ArrayList<UserBean>();
		String sql = "select id,name,displayname,email,phone,date,integral,vote from user where displayname like '%" + name + "%'";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);			
			while (result.next()) {
				UserBean u = new UserBean();
				u.setId(result.getInt(1));
				u.setName(result.getString(2));
				u.setDisplayname(result.getString(3));
				u.setEmail(result.getString(4));
				u.setPhone(result.getString(5));
				u.setDate(result.getDate(6));
				u.setIntegral(result.getInt(7));
				u.setVote(result.getString(8));
				list.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList<MusicBean> SearchMusic(String title) {
		ArrayList<MusicBean> list = new ArrayList<MusicBean>();
		String sql = "select id ,imagename,musicname,title,content,integral from uploadfile where title like '%" + title + "%'";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);			
			while (result.next()) {
				MusicBean u = new MusicBean();
				u.setId(result.getInt(1));
				u.setImagename(result.getString(2));
				u.setMusicname(result.getString(3));
				u.setTitle(result.getString(4));
				u.setContent(result.getString(5));
				u.setIntegral(result.getInt(6));
				list.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public void setCon(Connection con) {
		this.con = con;
	}

}
